﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

class AccessData
{
    public static void ProductsAndCategories(string connectionString)
    {
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        using (connection)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "Select p.ProductName, c.CategoryName from Products p join Categories c ON p.CategoryID = c.CategoryID " +
                                "group by  c.CategoryName, p.ProductName";
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine("product: {0} - category: {1}", reader["ProductName"], reader["CategoryName"]);
            }
        }
    }

    public static void AddProduct(string connectionString, string productName, int? supplierID, int? categoryID, string quantityPerUnit, double? unitPrice, int? unitsInStock,
        int? unitsOnOrder, int? reorderLevel, byte discontinued)
    {
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        using (connection)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = connection;

            command.Parameters.Add(new SqlParameter("@productName", productName));

            if (supplierID == null)
            {
                command.Parameters.Add(new SqlParameter("@SupplierID", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@SupplierID", supplierID));
            }

            if (categoryID == null)
            {
                command.Parameters.Add(new SqlParameter("@categoryID", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@categoryID", categoryID));
            }

            if (quantityPerUnit == null)
            {
                command.Parameters.Add(new SqlParameter("@quantityPerUnit", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@quantityPerUnit", quantityPerUnit));
            }

            if (unitPrice == null)
            {
                command.Parameters.Add(new SqlParameter("@unitPrice", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@unitPrice", unitPrice));
            }

            if (unitsInStock == null)
            {
                command.Parameters.Add(new SqlParameter("@unitsInStock", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@unitsInStock", unitsInStock));
            }

            if (unitsOnOrder == null)
            {
                command.Parameters.Add(new SqlParameter("@unitsOnOrder", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@unitsOnOrder", unitsOnOrder));
            }

            if (reorderLevel == null)
            {
                command.Parameters.Add(new SqlParameter("@reorderLevel", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@reorderLevel", reorderLevel));
            }

            command.Parameters.Add(new SqlParameter("@discontinued", discontinued));

            command.CommandText = "INSERT INTO Products ( productName, supplierId, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel,discontinued ) " +
                "VALUES( @productName, @supplierId, @categoryID, @quantityPerUnit, @unitPrice, @unitsInStock, @unitsOnOrder, @reorderLevel, @discontinued )";
            command.ExecuteNonQuery();
        }
    }

    static void Main()
    {
        string connectionString = @"Server=.\SQLEXPRESS;Database=Northwind;Integrated security=true";
        ProductsAndCategories(connectionString);
        Console.WriteLine("-------------------");
        AddProduct(connectionString, "Kiselo mlqko", null, null, null, null, null, null, null, 0);
    }
}
